import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class Professions {
	
	/* Db connection */
	Connection db;
	
	public ArrayList<String> getAllProfessions() {
		ArrayList<String> profs = new ArrayList<String>();
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT * FROM profession");
				while (rs.next()) {
					profs.add(rs.getString("name"));
					System.err.println("profession:" + 
							profs.get(profs.size() - 1));
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		
		return profs;
	}
	
	public String getNameById(int id) {
		String name = new String("");
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT * FROM profession");
				while (rs.next()) {
					if (id == rs.getInt("id")) {
						name = rs.getString("name");
						System.err.println("profession:" + 
								name);
					}
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		
		return name;
	}
	
	public int getIdByName(String name) {
		int id = 0;
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT * FROM profession");
				while (rs.next()) {
					if (name.equals(rs.getString("name"))) {
						id = rs.getInt("id");
						System.err.println("profession id:" + 
								(new Integer(id)).toString());
					}
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		
		return id;
	}
	
	public double getFactor(String name) {
		
		double factor = 0;
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT factor FROM profession where name ='" +
						name + "'");
				while (rs.next()) {
					factor = rs.getDouble("factor");
					System.err.println("factor:" + 
							(new Double(factor)).toString());
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}	
		
		return factor;
	}
	
	public ArrayList<String> getProducts() {
		
		ArrayList<String> products = new ArrayList<String>();
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT * FROM product");
				while (rs.next()) {
					products.add(rs.getString("name"));
					System.err.println("product:" + 
							products.get(products.size() - 1));
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}		
		
		return products;
	}
	
	public ArrayList<String> getDesiredProducts(String profession) {
		
		ArrayList<String> products = new ArrayList<String>();
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"select recproduct.name as \"prodname\", " +
							   "profession.name as \"profname\" " +
					    "from profession inner join recproduct " + 
					    "on profession.id = recproduct.profid " + 
					    "where profession.name = '" + profession + "';");
				while (rs.next()) {
					products.add(rs.getString("prodname"));
					System.err.println("product:" + 
							products.get(products.size() - 1));
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}		
		
		return products;
	}
	
	public double getProductEnergy(String name) {
		
		double energy = 0;
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT energy FROM product " + 
						"WHERE name = '" + name + "';");
				while (rs.next()) {
					energy = rs.getFloat("energy");
					System.err.println("energy:" + 
							(new Double(energy)).toString());
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}	
		
		return energy;
	}
	
    public ArrayList<String> getActivities() {
		
		ArrayList<String> activities = new ArrayList<String>();
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT * FROM activity");
				while (rs.next()) {
					activities.add(rs.getString("name"));
					System.err.println("activities:" + 
							activities.get(activities.size() - 1));
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}		
		
		return activities;
	}
	
    public ArrayList<String> getDesiredActivities(String profession) {
		
		ArrayList<String> activities = new ArrayList<String>();
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"select recactivity.name as \"actname\", " +
							   "profession.name as \"profname\" " +
					    "from profession inner join recactivity " + 
					    "on profession.id = recactivity.profid " + 
					    "where profession.name = '" + profession + "';");
				while (rs.next()) {
					activities.add(rs.getString("actname"));
					System.err.println("activities:" + 
							activities.get(activities.size() - 1));
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}		
		
		return activities;
	}
    
    public double getActivityEnergy(String name) {
		
		double energy = 0;
		
		if (db != null)
		{
			try {
				Statement st = db.createStatement();
				ResultSet rs = st.executeQuery(
						"SELECT energy FROM activity " + 
						"WHERE name = '" + name + "';");
				while (rs.next()) {
					energy = rs.getFloat("energy");
					System.err.println("energy:" + 
							(new Double(energy)).toString());
				}	
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}	
		
		return energy;
	}
    
	private void initDbConnection() {
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e)
		{
			System.err.println("Class org.postgresql.Driver was not found: " 
					+ e.getMessage());
		}
		
		try {
		    db = DriverManager.getConnection(
		    		"jdbc:postgresql:dietsystem",
					"dietsystem",
					"dietsystem");
		} catch (SQLException e) {
			System.err.println(
					"Can't connect to \"jdbc:postgresql:dietsystem\":  "
					+ e.getMessage());
		}
	}
	
	public Professions() {
		
		initDbConnection();
		
	}
	
	/* "Destructor" */
	protected void finalize() throws Throwable {
		try {
			db.close();
		} catch (SQLException e) {
			System.err.println(
					"Can't close connection to \"jdbc:postgresql:dietsystem\":  "
					+ e.getMessage());
		}
		finally {
			super.finalize();
		}
	}

}
